package com.ecsolutions.dao;

import com.ecsolutions.entity.*;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;

/**
 * Created by Administrator on 2017/7/31.
 */
@Repository
public interface Utilization_Dao {

    //币种类型
    @Select("select distinct ccy,ccyname from TACCYMAS")
    @ResultType(HashMap.class)
    List<HashMap<String, String>> getCcyList();

    //文档标识类型
    @Select("select distinct flagtext,CondIndflag from condindflagtext")
    @ResultType(HashMap.class)
    List<HashMap<String, String>> getFlagList();

    //额度号类型
    @Select("select distinct lineno,linedesc from loanapplicantfacilityinfo where osamt>0 And expirydate>=trunc(sysdate)")
    @ResultType(HashMap.class)
    List<HashMap<String, String>> getLinenoList();

    //方案类型
    @Select("select distinct schetyp,schedes from hkbinsch")
    @ResultType(HashMap.class)
    List<HashMap<String, String>> getFanganList();

    //利率类型
    @Select("select ratetyp,intrtpa from tainrmas where TRIM(ccycode) = TRIM(#{drawccy}) and effdate=(select max(effdate) from tainrmas)")
    @ResultType(HashMap.class)
    List<HashMap<String, Object>> getLilvList(@Param("drawccy") String drawccy);

    //年利率
    @Select("select intrtpa from tainrmas where TRIM(ccycode) = TRIM(#{drawccy}) And TRIM(ratetyp) = TRIM(#{ratetype}) and effdate=(select max(effdate) from tainrmas)")
    @ResultType(String.class)
    String getNianLilvInfo(@Param("drawccy") String drawccy, @Param("ratetype") String ratetype);

    //经理名称
    @Select("select descrip from taacomas where TRIM(accoffe) = TRIM(#{accoffic})")
    @ResultType(String.class)
    String getJingLiInfo(String accoffic);

    //门类
    @Select("SELECT MENLEI,LEIBIEMINGCHENG FROM HANGYE WHERE DALEI is null AND ZHONGLEI is null AND XIAOLEI is null")
    @ResultType(HashMap.class)
    List<HashMap<String, String>> getMengleiList();

    //大类
    @Select("SELECT DALEI,LEIBIEMINGCHENG FROM HANGYE WHERE TRIM(MENLEI) = TRIM(#{dircflag1}) AND DALEI is not null AND ZHONGLEI is null AND XIAOLEI is null")
    @ResultType(HashMap.class)
    List<HashMap<String, String>> getDaleiList(@Param("dircflag1") String dircflag1);

    //中类
    @Select("SELECT ZHONGLEI,LEIBIEMINGCHENG FROM HANGYE WHERE TRIM(MENLEI) = TRIM(#{dircflag1}) AND TRIM(DALEI) = TRIM(#{dircflag2}) AND ZHONGLEI is not null AND XIAOLEI is null")
    @ResultType(HashMap.class)
    List<HashMap<String, String>> getZhongleiList(@Param("dircflag1") String dircflag1, @Param("dircflag2") String dircflag2);

    //小类
    @Select("SELECT XIAOLEI,LEIBIEMINGCHENG FROM HANGYE WHERE TRIM(MENLEI) = TRIM(#{dircflag1}) AND TRIM(DALEI) = TRIM(#{dircflag2}) AND TRIM(ZHONGLEI) = TRIM(#{dircflag3}) AND XIAOLEI is not null")
    @ResultType(HashMap.class)
    List<HashMap<String, String>> getXiaoleiList(@Param("dircflag1") String dircflag1, @Param("dircflag2") String dircflag2, @Param("dircflag3") String dircflag3);

    //通过custcod查询出的信息
    @Select("select * from utilizationdetail where TRIM(custcod) = TRIM(#{custcod})")
    @ResultType(Utilization_Entity.class)
    List<Utilization_Entity> getByCustcodInfo(String custcod);

    //通过custcod和loanref查询出还款计划表的信息
    @Select("select * from paymentschedule where TRIM(custcod) = TRIM(#{custcod}) AND TRIM(loanref) = TRIM(#{loanref})")
    @ResultType(Pay_Entity.class)
    List<Pay_Entity> getPayList(@Param("custcod") String custcod, @Param("loanref") String loanref);

    //通过loanref查询出客户申请信息
    @Select("select * from loanapplicationinfo where TRIM(loanref) = TRIM(#{loanref})")
    @ResultType(ApplicationInfo_Entity.class)
    ApplicationInfo_Entity getByLoanrefInfo(String loanref);

    //保存客户申请信息
    @Insert({"insert into loanapplicationinfo(custcode,line_no,loanref,applicationdate,applicationamount,rate,deadline,loanpurpose,loanpurposedesc,fluserid)", "values(#{custcode,jdbcType=VARCHAR},#{line_no,jdbcType=VARCHAR},#{loanref,jdbcType=VARCHAR},#{applicationdate,jdbcType=TIMESTAMP},#{applicationamount,jdbcType=NUMERIC},#{rate,jdbcType=NUMERIC},#{deadline,jdbcType=NUMERIC},#{loanpurpose,jdbcType=CHAR},#{loanpurposedesc,jdbcType=VARCHAR},#{fluserid,jdbcType=VARCHAR})"})
    void saveApplicationInfo(ApplicationInfo_Entity applicationInfo_entity);

    //更新客户申请信息
    @Update("update loanapplicationinfo set applicationdate=#{applicationdate,jdbcType=TIMESTAMP},applicationamount=#{applicationamount,jdbcType=NUMERIC},rate=#{rate,jdbcType=NUMERIC},deadline=#{deadline,jdbcType=NUMERIC},loanpurpose=#{loanpurpose,jdbcType=CHAR},loanpurposedesc=#{loanpurposedesc,jdbcType=VARCHAR},fluserid=#{fluserid,jdbcType=VARCHAR} where loanref=#{loanref}")
    void updateApplicationInfo(ApplicationInfo_Entity applicationInfo_entity);

    //保存使用信息后通过放款额更新授信表中的余额和可用余额和标识
    @Update("update loanapplicantfacilityinfo set osamt=#{osamt,jdbcType=NUMERIC},avliamt=#{avliamt,jdbcType=NUMERIC},linestatus=#{linestatus,jdbcType=VARCHAR} where custcod=#{custcod} and lineno=#{lineno}")
    Boolean updateFacilityinfo(@Param("osamt") Integer osamt,@Param("avliamt") Integer avliamt,@Param("linestatus") String linestatus,@Param("custcod") String custcod,@Param("lineno") String lineno);

    //保存使用信息后通过放款额更新授信表中的可用余额和冻结金额和标识
    @Update("update loanapplicantfacilityinfo set avliamt=#{avliamt,jdbcType=NUMERIC},holdamt=#{holdamt,jdbcType=NUMERIC},linestatus=#{linestatus,jdbcType=VARCHAR} where custcod=#{custcod} and lineno=#{lineno}")
    Boolean updateFacilityinfoTwo(@Param("avliamt") Integer avliamt,@Param("holdamt") Integer holdamt,@Param("linestatus") String linestatus,@Param("custcod") String custcod,@Param("lineno") String lineno);

    // 通过custcod和lineno查询出的Facility_Entity信息
    @Select("select * from loanapplicantfacilityinfo where TRIM(custcod) = TRIM(#{custcod}) AND TRIM(lineno) = TRIM(#{lineno})")
    @ResultType(Facility_Entity.class)
    Facility_Entity getByCustcodAndLinenoInfo(@Param("custcod") String custcod, @Param("lineno") String lineno);

    //通过custcod和lineno和loanref查询出的Utilization_Entity信息
    @Select("select * from utilizationdetail where TRIM(custcod) = TRIM(#{custcod}) AND TRIM(lineno) = TRIM(#{lineno}) AND TRIM(loanref) = TRIM(#{loanref})")
    @ResultType(Utilization_Entity.class)
    Utilization_Entity getUtilizationByCustcodAndLineno(@Param("custcod") String custcod, @Param("lineno") String lineno, @Param("loanref") String loanref);

    //查询出授信表信息通过bpm_no
    @Select("select * from loanapplicantfacilityinfo where TRIM(bpm_no) = TRIM(#{bpm_no})")
    @ResultType(Facility_Entity.class)
    Facility_Entity getFacilityInfoByBpm_no(@Param("bpm_no") String bpm_no);

    //查询出使用表信息通过bpm_no
    @Select("select * from utilizationdetail where TRIM(bpm_no) = TRIM(#{bpm_no})")
    @ResultType(Utilization_Entity.class)
    Utilization_Entity getUtilizationInfoByBpm_no(@Param("bpm_no") String bpm_no);

  //查询分支行
    @Select("select bankCode from LoanApplicant where TRIM(custcod) = TRIM(#{custcod})")
    @ResultType(String.class)
    String getBankcode(@Param("custcod") String custcode);

    //查询中文名
    @Select("select lastName from LoanApplicant where TRIM(custcod) = TRIM(#{custcod})")
    @ResultType(String.class)
    String getLastname(@Param("custcod") String custcode);

    //查询中文名和分支行
    @Select("select lastName,bankCode from LoanApplicant where TRIM(custcod) = TRIM(#{custcod})")
    @ResultType(HashMap.class)
    HashMap getLoanApplicant(@Param("custcod") String custcode);

    //查询支付信息集合
    @Select("select * from loanpaymentinfo_det where TRIM(custcode) = TRIM(#{custcode}) AND TRIM(loanref) = TRIM(#{loanref})")
    @ResultType(Loanpaymentinfo_det_Entity.class)
    List<Loanpaymentinfo_det_Entity> getLoanApplicantInfoList(@Param("custcode") String custcode, @Param("loanref") String loanref);

    //查询使用支付信息
    @Select("select * from LoanPaymentInfo where TRIM(loanref) = TRIM(#{loanref})")
    @ResultType(Loanpaymentinfo_Entity.class)
    Loanpaymentinfo_Entity getLoanPaymentInfo(@Param("loanref") String loanref);

    //查询使用支付2信息
    @Select("select * from LoanPaymentInfo_det where TRIM(loanref) = TRIM(#{loanref})")
    @ResultType(Loanpaymentinfo_det_Entity.class)
    Loanpaymentinfo_det_Entity getLoanPayment_detInfo(@Param("loanref") String loanref);

    //查询出最大的id
    @Select("select Max(id) from LoanPaymentInfo_det")
    @ResultType(Integer.class)
    Integer getMaxId();

    //保存使用信息
    @Insert({"insert into utilizationdetail(loanref,lineno,custcod,bpm_no,linedesc,lineccy,lineamt,avliamt,drawdate,anndate,schetype,drawccy,drawamt,ratetype,rate,spread,percent,schemarate,overdurate,paymentmethod,tenor,fixflag,payfreq,payper,firstpaydate,collflag,docflag,dircflag1,dircflag2,dircflag3,dircflag4,dircdesc,loanfreq,micprod,eduyear,marginamt,chgtype,chgamt,chgdate,accoffic,accofname,contractsymbol,signeddate,inaccname,inaccno,outacname,outaccno,fluserid,Duedate)", "values(#{loanref,jdbcType=VARCHAR},#{lineno,jdbcType=VARCHAR},#{custcod,jdbcType=VARCHAR},#{bpm_no,jdbcType=VARCHAR},#{linedesc,jdbcType=VARCHAR},#{lineccy,jdbcType=VARCHAR},#{lineamt,jdbcType=NUMERIC},#{avliamt,jdbcType=NUMERIC},#{drawdate,jdbcType=TIMESTAMP},#{anndate,jdbcType=TIMESTAMP},#{schetype,jdbcType=VARCHAR},#{drawccy,jdbcType=VARCHAR},#{drawamt,jdbcType=NUMERIC},#{ratetype,jdbcType=VARCHAR},#{rate,jdbcType=NUMERIC},#{spread,jdbcType=NUMERIC},#{percent,jdbcType=NUMERIC},#{schemarate,jdbcType=NUMERIC},#{overdurate,jdbcType=NUMERIC},#{paymentmethod,jdbcType=VARCHAR},#{tenor,jdbcType=NUMERIC},#{fixflag,jdbcType=VARCHAR},#{payfreq,jdbcType=NUMERIC},#{payper,jdbcType=VARCHAR},#{firstpaydate,jdbcType=TIMESTAMP},#{collflag,jdbcType=VARCHAR},#{docflag,jdbcType=VARCHAR},#{dircflag1,jdbcType=VARCHAR},#{dircflag2,jdbcType=VARCHAR},#{dircflag3,jdbcType=VARCHAR},#{dircflag4,jdbcType=VARCHAR},#{dircdesc,jdbcType=VARCHAR},#{loanfreq,jdbcType=VARCHAR},#{micprod,jdbcType=VARCHAR},#{eduyear,jdbcType=NUMERIC},#{marginamt,jdbcType=NUMERIC},#{chgtype,jdbcType=VARCHAR},#{chgamt,jdbcType=NUMERIC},#{chgdate,jdbcType=TIMESTAMP},#{accoffic,jdbcType=VARCHAR},#{accofname,jdbcType=VARCHAR},#{contractsymbol,jdbcType=VARCHAR},#{signeddate,jdbcType=TIMESTAMP},#{inaccname,jdbcType=VARCHAR},#{inaccno,jdbcType=VARCHAR},#{outacname,jdbcType=VARCHAR},#{outaccno,jdbcType=VARCHAR},#{fluserid,jdbcType=VARCHAR},#{Duedate,jdbcType=TIMESTAMP})"})
    void saveUtilizationInfo(Utilization_Entity utilization_entity);

    //更新使用信息
    @Update("update utilizationdetail set drawdate=#{drawdate,jdbcType=TIMESTAMP},anndate=#{anndate,jdbcType=TIMESTAMP},schetype=#{schetype,jdbcType=VARCHAR},drawccy=#{drawccy,jdbcType=VARCHAR},drawamt=#{drawamt,jdbcType=NUMERIC},ratetype=#{ratetype,jdbcType=VARCHAR},rate=#{rate,jdbcType=NUMERIC},spread=#{spread,jdbcType=NUMERIC},percent=#{percent,jdbcType=NUMERIC},schemarate=#{schemarate,jdbcType=NUMERIC},overdurate=#{overdurate,jdbcType=NUMERIC},paymentmethod=#{paymentmethod,jdbcType=VARCHAR},tenor=#{tenor,jdbcType=NUMERIC},fixflag=#{fixflag,jdbcType=VARCHAR},payfreq=#{payfreq,jdbcType=NUMERIC},payper=#{payper,jdbcType=VARCHAR},firstpaydate=#{firstpaydate,jdbcType=TIMESTAMP},collflag=#{collflag,jdbcType=VARCHAR},docflag=#{docflag,jdbcType=VARCHAR},dircflag1=#{dircflag1,jdbcType=VARCHAR},dircflag2=#{dircflag2,jdbcType=VARCHAR},dircflag3=#{dircflag3,jdbcType=VARCHAR},dircflag4=#{dircflag4,jdbcType=VARCHAR},dircdesc=#{dircdesc,jdbcType=VARCHAR},loanfreq=#{loanfreq,jdbcType=VARCHAR},micprod=#{micprod,jdbcType=VARCHAR},eduyear=#{eduyear,jdbcType=NUMERIC},marginamt=#{marginamt,jdbcType=NUMERIC},chgtype=#{chgtype,jdbcType=VARCHAR},chgamt=#{chgamt,jdbcType=NUMERIC},chgdate=#{chgdate,jdbcType=TIMESTAMP},accoffic=#{accoffic,jdbcType=VARCHAR},accofname=#{accofname,jdbcType=VARCHAR},contractsymbol=#{contractsymbol,jdbcType=VARCHAR},signeddate=#{signeddate,jdbcType=TIMESTAMP},inaccname=#{inaccname,jdbcType=VARCHAR},inaccno=#{inaccno,jdbcType=VARCHAR},outacname=#{outacname,jdbcType=VARCHAR},outaccno=#{outaccno,jdbcType=VARCHAR},fluserid=#{fluserid,jdbcType=VARCHAR},Duedate=#{Duedate,jdbcType=TIMESTAMP} where TRIM(custcod)=TRIM(#{custcod}) and TRIM(lineno)=TRIM(#{lineno}) and TRIM(loanref)=TRIM(#{loanref})")
    void updateUtiltzationInfo(Utilization_Entity utilization_entity);

    //删除使用信息
    @Delete("delete from utilizationdetail where TRIM(custcod)=TRIM(#{custcod}) and TRIM(lineno)=TRIM(#{lineno}) and TRIM(loanref)=TRIM(#{loanref})")
    void deleteUtilizationInfo(@Param("custcod") String custcod, @Param("lineno") String lineno, @Param("loanref") String loanref);

    //保存使用支付信息
    @Insert({"insert into loanpaymentinfo(custCode,lineno,loanRef,nmgflag,loantype,loancatg,zcxflag,duiFlag,purpflag,purpdesc,paytype,fenzhihang,lastname,drawccy,drawamt,drawdate)", "values(#{custCode,jdbcType=VARCHAR},#{lineno,jdbcType=VARCHAR},#{loanRef,jdbcType=CHAR},#{nmgflag,jdbcType=CHAR},#{loantype,jdbcType=CHAR},#{loancatg,jdbcType=CHAR},#{zcxflag,jdbcType=CHAR},#{duiFlag,jdbcType=CHAR},#{purpflag,jdbcType=CHAR},#{purpdesc,jdbcType=CHAR},#{paytype,jdbcType=CHAR},#{fenzhihang,jdbcType=VARCHAR},#{lastname,jdbcType=VARCHAR},#{drawccy,jdbcType=CHAR},#{drawamt,jdbcType=VARCHAR},#{drawdate,jdbcType=VARCHAR})"})
    void saveLoanpaymentInfo(Loanpaymentinfo_Entity loanpaymentinfo_entity);

    //更新使用支付信息
    @Update("update loanpaymentinfo set custCode=#{custCode,jdbcType=VARCHAR},lineno=#{lineno,jdbcType=VARCHAR},loanRef=#{loanRef,jdbcType=CHAR},nmgflag=#{nmgflag,jdbcType=CHAR},loantype=#{loantype,jdbcType=CHAR},loancatg=#{loancatg,jdbcType=CHAR},zcxflag=#{zcxflag,jdbcType=CHAR},duiFlag=#{duiFlag,jdbcType=CHAR},purpflag=#{purpflag,jdbcType=CHAR},purpdesc=#{purpdesc,jdbcType=CHAR},paytype=#{paytype,jdbcType=CHAR},fenzhihang=#{fenzhihang,jdbcType=VARCHAR},lastname=#{lastname,jdbcType=VARCHAR},drawccy=#{drawccy,jdbcType=CHAR},drawamt=#{drawamt,jdbcType=VARCHAR},drawdate=#{drawdate,jdbcType=VARCHAR} where loanRef=#{loanRef}")
    void updateLoanpaymentInfo(Loanpaymentinfo_Entity loanpaymentinfo_entity);

    //保存使用支付信息2
    @Insert({"insert into LoanPaymentInfo_det(id,custcode,loanref,duiflag,cityflag,payamt,paydate,duicustcd,duibranch,duiaccno,duiname,waibranch,waibrname,waiaccno,wainame)", "values(#{id,jdbcType=NUMERIC},#{custcode,jdbcType=VARCHAR},#{loanref,jdbcType=VARCHAR},#{duiflag,jdbcType=CHAR},#{cityflag,jdbcType=CHAR},#{payamt,jdbcType=NUMERIC},#{paydate,jdbcType=TIMESTAMP},#{duicustcd,jdbcType=VARCHAR},#{duibranch,jdbcType=VARCHAR},#{duiaccno,jdbcType=VARCHAR},#{duiname,jdbcType=VARCHAR},#{waibranch,jdbcType=VARCHAR},#{waibrname,jdbcType=VARCHAR},#{waiaccno,jdbcType=VARCHAR},#{wainame,jdbcType=VARCHAR})"})
    void saveLoanpaymentInfodet(Loanpaymentinfo_det_Entity loanpaymentinfo_det_entity);

    //更新使用支付信息2
    @Update("update LoanPaymentInfo_det set duiflag=#{duiflag,jdbcType=CHAR},cityflag=#{cityflag,jdbcType=CHAR},payamt=#{payamt,jdbcType=NUMERIC},paydate=#{paydate,jdbcType=TIMESTAMP},duicustcd=#{duicustcd,jdbcType=VARCHAR},duibranch=#{duibranch,jdbcType=VARCHAR},duiaccno=#{duiaccno,jdbcType=VARCHAR},duiname=#{duiname,jdbcType=VARCHAR},waibranch=#{waibranch,jdbcType=VARCHAR},waibrname=#{waibrname,jdbcType=VARCHAR},waiaccno=#{waiaccno,jdbcType=VARCHAR},wainame=#{wainame,jdbcType=VARCHAR} where id =#{id}")
    void updateLoanpaymentInfodet(Loanpaymentinfo_det_Entity loanpaymentinfo_det_entity);

    //删除使用信息
    @Delete("delete from LoanPaymentInfo_det where TRIM(id)=TRIM(#{id})")
    void deleteLoanpaymentInfodet(@Param("id") Integer id);

    //计算出还款计划表总利息，总还款额，总本金
    @Select("SELECT Sum(instamt) as sum_instamt,Sum(prnamt) as sum_prnamt,Sum(intamt) as sum_intamt FROM paymentSchedule WHERE loanref =#{loanref}")
    @ResultType(ExcelDownld_Entity.class)
    ExcelDownld_Entity getSumInfo(String loanref);
}
